package com.smart4j.chapter.common.pool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * 针对数据库连接池获取连接和直接获取连接 执行查询情况，效率对比。
 * @author w
 */
public class PoolTest {
	public static void main(String[] args) {
		// 执行查询的次数
		int sum = 100;
		long start = System.currentTimeMillis();
		poolsExecute(sum);
		System.out.println("使用连接池耗时："+(System.currentTimeMillis()-start)+" ms");
		start = System.currentTimeMillis();
		connExecute(sum);
		System.out.println("直接获取连接耗时："+(System.currentTimeMillis()-start)+" ms");
	}
	
	/**
	 * 使用连接池，获取连接，执行查询
	 * @param sum
	 */
	private static void poolsExecute(int sum) {
		for (int i = 0; i < sum; i++) {
			Connection connection = PoolUtils.getInstance().getConnection();
			try {
				query(connection,sum);
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				// 释放资源
				PoolUtils.getInstance().release(connection);
			}
		}
	}
	
	/**
	 * 直接获取连接，不使用连接池 执行查询
	 * @param sum 占位符
	 */
	private static void connExecute(int sum) {
		for (int i = 0; i < sum; i++) {
			Connection connection = PoolUtils.getConnection(sum);
			query(connection,sum);
			// 增加是否关闭连接，测试耗时情况
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 具体执行查询的方法 
	 * @param connection  数据库连接对象
	 * @param sum 占位符 
	 */
	private static void query(Connection connection,int sum) {
		String sql = "SELECT * from client limit 1";
		try {
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			ResultSet resultSet = prepareStatement.executeQuery();
			while(resultSet.next()){
				String id = resultSet.getString("id");
				String name = resultSet.getString("name");
			//	System.out.println("id :"+id + "  name: "+name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
